﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using DTO;

namespace DAO
{
    public class PhieuThuePhongDAO
    {
        private SqlConnection con;
        private DataProvider dp;

        public PhieuThuePhongDAO()
        {
            dp = new DataProvider();
        }

        public List<PhongInfo> DanhSachPhongTrong()
        {
            List<PhongInfo> ds = new List<PhongInfo>();
            con = dp.MoKetNoi();
            string sql = "SELECT a.Ma_Phong as MaPhong, a.TenPhong as TenPhong, a.Mota as MoTa, a.Ma_LoaiPhong as MaLoaiPhong, b.TenLoaiPhong as TenLoaiPhong, b.Gia as Gia, b.SoKhachToiDa as SoKhachToiDa ";
            sql += "FROM Phong a, LoaiPhong b ";
            sql += "WHERE a.Ma_LoaiPhong=b.Ma_LoaiPhong AND LTRIM(RTRIM(a.TinhTrang))='N'";
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                PhongInfo p = new PhongInfo();
                p.MaPhong = dr.GetString(0);
                p.TenPhong = dr.GetString(1);
                p.MoTa = dr.GetString(2);
                p.LoaiPhong.Maloaiphong = dr.GetString(3).Trim();
                p.LoaiPhong.Tenloaiphong = dr.GetString(4);
                p.LoaiPhong.Gia = (double)dr.GetDecimal(5);
                p.LoaiPhong.Soktd = dr.GetInt32(6);
                ds.Add(p);
            }
            dp.DongKetNoi(con);
            return ds;
        }

        public PhieuThueInfo LayPhieuThuePhong(int MaPhieu)
        {
            PhieuThueInfo p = new PhieuThueInfo();
            con = dp.MoKetNoi();
            string sql = "SELECT a.Ma_PT as MaPhieu, b.Ma_Phong, b.TenPhong, c.TenLoaiPhong as LoaiPhong, d.HoTen as HoTen, a.NgayBD as NgayBD, a.NgayKT as NgayKT, d.DiaChi, c.Gia, a.TienDatCoc ";
            sql += "FROM PhieuThuePhong a, Phong b, LoaiPhong c , KhachHang d ";
            sql += "WHERE a.Ma_Phong=b.Ma_Phong AND b.Ma_LoaiPhong=c.Ma_LoaiPhong and LTRIM(RTRIM(b.TinhTrang))='Y' and a.NguoiThue=d.Ma_KH and a.Ma_PT=@MaPhieu";
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlParameter param = cmd.Parameters.Add("@MaPhieu", SqlDbType.Int);
            param.Value = MaPhieu;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                p.MaPhieu = dr.GetInt32(0);
                p.MaPhong = dr.GetString(1);
                p.TenPhong = dr.GetString(2);
                p.LoaiPhong = dr.GetString(3);
                p.HoTen = dr.GetString(4);
                p.NgayBD = dr.GetDateTime(5);
                p.NgayKT = dr.GetDateTime(6);
                p.DiaChi = dr.GetString(7);
                p.Gia = (double)dr.GetDecimal(8);
                p.TienDatCoc = (double)dr.GetDecimal(9);
            }
            dp.DongKetNoi(con);
            return p;
        }

        public List<PhieuThueInfo> LayDanhSachPhieuThuePhong()
        {
            List<PhieuThueInfo> ds = new List<PhieuThueInfo>();
            con = dp.MoKetNoi();
            string sql = "SELECT a.Ma_PT as MaPhieu, b.TenPhong, c.TenLoaiPhong as LoaiPhong, d.HoTen as HoTen, a.NgayBD as NgayBD, a.NgayKT as NgayKT ";
            sql += "FROM PhieuThuePhong a, Phong b, LoaiPhong c , KhachHang d ";
            sql += "WHERE a.Ma_Phong=b.Ma_Phong AND b.Ma_LoaiPhong=c.Ma_LoaiPhong and LTRIM(RTRIM(b.TinhTrang))='Y' and a.NguoiThue=d.Ma_KH";
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                PhieuThueInfo p = new PhieuThueInfo();
                p.MaPhieu = dr.GetInt32(0);
                p.TenPhong = dr.GetString(1);
                p.LoaiPhong = dr.GetString(2);
                p.HoTen = dr.GetString(3);
                p.NgayBD = dr.GetDateTime(4);
                p.NgayKT = dr.GetDateTime(5);
                ds.Add(p);
            }
            dp.DongKetNoi(con);
            return ds;
        }

        public int ThemPhieuThuePhong(PhieuThuePhongDTO phieu, List<int> dskh)
        {
            //them phieu, cap nhat phong da thue thanh tinh trang da thue
            con = dp.MoKetNoi();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "sp_ThemPhieuThuePhong";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = cmd.Parameters.Add("@MaPhong", SqlDbType.NVarChar);
            param.Value = phieu.MaPhong;

            param = cmd.Parameters.Add("@NgayBD", SqlDbType.DateTime);
            param.Value = phieu.NgayBD;

            param = cmd.Parameters.Add("@NgayKT", SqlDbType.DateTime);
            param.Value = phieu.NgayKT;

            param = cmd.Parameters.Add("@TienDatCoc", SqlDbType.Money);
            param.Value = phieu.TienDatCoc;

            param = cmd.Parameters.Add("@MaNV", SqlDbType.VarChar);
            //param.Value = phieu.MaNV;
            param.Value = "nv1";

            param = cmd.Parameters.Add("@NguoiThue", SqlDbType.Int);
            param.Value = dskh[0];

            int MaPhieuThue = (int)cmd.ExecuteScalar();

            //them vao chi tiet phieu thue
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "sp_ThemChiTietPTPhong";
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(int makh in dskh)
            {
                cmd.Parameters.Clear();
                param = cmd.Parameters.Add("@MaPhieuThue", SqlDbType.Int);
                param.Value = MaPhieuThue;
                param = cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                param.Value = makh;
                param = cmd.Parameters.Add("@NguoiThue", SqlDbType.Int);
                param.Value = dskh[0];
                cmd.ExecuteNonQuery();
            }

            cmd.Dispose();
            dp.DongKetNoi(con);
            return MaPhieuThue;
        }

        public int LaySoKhachToiDaTheoLoaiPhong(string MaLoaiPhong) {
            string sql = "select SoKhachToiDa from LoaiPhong where Ma_LoaiPhong=@MaLoaiPhong";
            con = dp.MoKetNoi();
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlParameter param = cmd.Parameters.Add("@MaLoaiPhong", SqlDbType.NChar);
            param.Value = MaLoaiPhong;
            int i = (int)cmd.ExecuteScalar();
            return i;
        }

        public int LayGiaPhongTheoLoaiPhong(string MaLoaiPhong)
        {
            string sql = "select Gia from LoaiPhong where Ma_LoaiPhong=@MaLoaiPhong";
            con = dp.MoKetNoi();
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlParameter param = cmd.Parameters.Add("@MaLoaiPhong", SqlDbType.NChar);
            param.Value = MaLoaiPhong;
            int i = (int)double.Parse(cmd.ExecuteScalar().ToString());
            return i;
        }

        public PhongInfo LayThongTinPhong(string MaPhong)
        {
            string sql = "SELECT a.Ma_Phong as MaPhong, a.TenPhong as TenPhong, a.Mota as MoTa, a.Ma_LoaiPhong as MaLoaiPhong, b.TenLoaiPhong as TenLoaiPhong, b.Gia as Gia, b.SoKhachToiDa as SoKhachToiDa ";
            sql += "FROM Phong a, LoaiPhong b ";
            sql += "WHERE a.Ma_LoaiPhong=b.Ma_LoaiPhong AND a.Ma_Phong=@MaPhong";
            con = dp.MoKetNoi();
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlParameter param = cmd.Parameters.Add("@MaPhong", SqlDbType.NVarChar);
            param.Value = MaPhong;
            PhongInfo p = new PhongInfo();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                p.MaPhong = dr.GetString(0);
                p.TenPhong = dr.GetString(1);
                p.MoTa = dr.GetString(2);
                p.LoaiPhong.Maloaiphong = dr.GetString(3).Trim();
                p.LoaiPhong.Tenloaiphong = dr.GetString(4);
                p.LoaiPhong.Gia = (double)dr.GetDecimal(5);
                p.LoaiPhong.Soktd = dr.GetInt32(6);
            }
            return p;
        }

        public List<PhongDTO> LayPhongTheoMaLoaiPhong(string MaLoaiPhong)
        {
            List<PhongDTO> dsp = new List<PhongDTO>();
            SqlConnection connection = dp.MoKetNoi();
            string sql = "SELECT * FROM Phong WHERE Ma_LoaiPhong = '" + MaLoaiPhong.Trim() + "' AND LTRIM(RTRIM(TinhTrang))='N'";
            SqlCommand command = new SqlCommand(sql, connection);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                PhongDTO p = new PhongDTO();
                p.Maphong = reader.GetString(0);
                p.Tenphong = reader.GetString(1);
                p.Mota = reader.GetString(2);
                p.Tinhtrang = reader.GetString(3);
                p.Maloaiphong = MaLoaiPhong;
                dsp.Add(p);
            }
            dp.DongKetNoi(connection);
            return dsp;
        }
    }
}
